﻿USE [iKBSystem]
GO

----- sp_QueryDomainScopes -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryDomainScopes]'))
DROP PROCEDURE [dbo].[sp_QueryDomainScopes]
GO
CREATE PROCEDURE [sp_QueryDomainScopes](
	@Type int,
	@Keyword NVARCHAR(MAX)
)
AS
BEGIN
	DECLARE @SqlExp AS nvarchar(Max);
	DECLARE @WhereExp AS nvarchar(Max);

	SET @SqlExp = 'SELECT [Key],[Name],[Code],[Type],[Description] FROM [dbo].[DomainScope]';
	SET @WhereExp = ' WHERE [Type] > 0';
	IF(@Type IS NOT NULL AND @Type > 0)
		SET @WhereExp = @WhereExp +  ' AND (([Type] & ' + CONVERT(NVARCHAR(20), @Type) + ') > 0)';
	IF(@Keyword IS NOT NULL)
	BEGIN
		SET @Keyword = dbo.ReformatLikeStatement(@Keyword);		
		IF(@Keyword <> '')
			SET @WhereExp = @WhereExp +  ' AND ([Name] LIKE ''%' + @Keyword + '%'' OR [Code] LIKE ''%' + @Keyword + '%'')';
	END

	SET @SqlExp = @SqlExp + @WhereExp;
	exec (@SqlExp);
END
GO

----- sp_GetDomainScopeByKey -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeByKey]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeByKey]
GO
CREATE PROCEDURE [sp_GetDomainScopeByKey](
	@Key uniqueidentifier
)
AS
BEGIN
	SELECT [Key],[Name],[Code],[Type],[Description] FROM [dbo].[DomainScope] WHERE [Key]=@Key;	
END
GO

----- sp_GetDomainScopeByCode -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeByCode]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeByCode]
GO
CREATE PROCEDURE [sp_GetDomainScopeByCode](
	@Code varchar(max)
)
AS
BEGIN
	SELECT [Key],[Name],[Code],[Type],[Description] FROM [dbo].[DomainScope] WHERE [Code]=@Code;	
END
GO

----- sp_AddOrUpdateDomainScope -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddOrUpdateDomainScope]'))
DROP PROCEDURE [dbo].[sp_AddOrUpdateDomainScope]
GO
CREATE PROCEDURE sp_AddOrUpdateDomainScope(
	@Name [nvarchar](50),
	@Code [varchar](20),
	@Type [int],
	@Description [nvarchar](200)
)
AS
BEGIN
	DECLARE @Key AS [uniqueidentifier];
	SET @Key = NULL;
	SELECT @Key = [Key] FROM [dbo].[DomainScope] WHERE [Code] = @Code;
	IF(@Key IS NULL)
		BEGIN
			SET @Key = NEWID();
			INSERT INTO [dbo].[DomainScope]([Key], [Name],[Code],[Type],[Description])
				VALUES(@Key, @Name, @Code, @Type, @Description);
		END
	ELSE
		BEGIN
			UPDATE [dbo].[DomainScope]
				SET [Name] = @Name, [Type]=@Type, [Description] = @Description
			WHERE [Key]=@Key;			
		END
	SELECT @Key AS 'Key';
END
GO

----- sp_AddOrUpdateDomainScopeUser -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddOrUpdateDomainScopeUser]'))
DROP PROCEDURE [dbo].[sp_AddOrUpdateDomainScopeUser]
GO
CREATE PROCEDURE sp_AddOrUpdateDomainScopeUser(
	@PersonKey [uniqueidentifier],
	@DomainScopeKey [uniqueidentifier],
	@ThirdPartyIdentity [nvarchar](200),
	@Description [nvarchar](200),
	@MonitorStatus [bit],
	@Stamp datetime
)
AS
BEGIN
	DECLARE @Key AS uniqueidentifier;
	SET @Key = NULL;	
	
	SELECT @Key = [Key] FROM [dbo].[DomainScopeUser]
	WHERE [DomainScopeKey] = @DomainScopeKey AND [ThirdPartyIdentity] = @ThirdPartyIdentity;	
	IF(@Key IS NULL)
		BEGIN
			SET @Key = NEWID();
			INSERT INTO [dbo].[DomainScopeUser] ([Key],[PersonKey],[DomainScopeKey],[ThirdPartyIdentity],[Description],[MonitorStatus],[CreatedStamp],[LastUpdatedStamp])
				VALUES(@Key, @PersonKey, @DomainScopeKey, @ThirdPartyIdentity, @Description, @MonitorStatus, @Stamp, @Stamp);
		END
	ELSE
		BEGIN
			UPDATE [dbo].[DomainScopeUser]
				SET [Description] = @Description, [LastUpdatedStamp]=@Stamp, [MonitorStatus]=@MonitorStatus
				WHERE [Key] = @Key;
		END
	
	SELECT @Key;
END
GO

----- sp_GetDomainScopeUserByKey -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeUserByKey]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeUserByKey]
GO
CREATE PROCEDURE sp_GetDomainScopeUserByKey(
	@Key [uniqueidentifier]
)
AS
BEGIN
	SELECT [UserKey]
      ,[PersonKey]
      ,[ScopeKey]
      ,[ThirdPartyIdentity]
      ,[UserDescription]
	  ,[MonitorStatus]
      ,[UserCreatedStamp]
      ,[UserLastUpdatedStamp]
      ,[ScopeName]
      ,[ScopeCode]
      ,[ScopeType]
      ,[ScopeDescription]
  FROM [dbo].[View_DomainScopeUsers]
  WHERE [UserKey] = @Key
  ORDER BY [ScopeName], [ThirdPartyIdentity];
END
GO

----- sp_GetDomainScopeUsers -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeUsers]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeUsers]
GO
CREATE PROCEDURE sp_GetDomainScopeUsers
AS
BEGIN
	SELECT [UserKey]
      ,[PersonKey]
      ,[ScopeKey]
      ,[ThirdPartyIdentity]
      ,[UserCreatedStamp]
      ,[UserLastUpdatedStamp]
	  ,[MonitorStatus]
      ,[ScopeName]
      ,[ScopeCode]
      ,[ScopeType]
  FROM [dbo].[View_DomainScopeUsers]
  ORDER BY [ScopeName], [ThirdPartyIdentity];
END
GO


----- sp_QueryDomainScopeUser -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryDomainScopeUser]'))
DROP PROCEDURE [dbo].[sp_QueryDomainScopeUser]
GO
CREATE PROCEDURE sp_QueryDomainScopeUser(
	@DomainScopeKey [uniqueidentifier],
	@ThirdPartyIdentity [nvarchar](200)
)
AS
BEGIN
	DECLARE @WhereCondition AS NVARCHAR(MAX);
	SET @WhereCondition = '';
	
	IF(@DomainScopeKey IS NOT NULL)
		SET @WhereCondition = @WhereCondition + ' [ScopeKey] = ''' + CONVERT(NVARCHAR(200),@DomainScopeKey) + '''';
		
	IF(@ThirdPartyIdentity IS NOT NULL)
	BEGIN
		IF NOT(@WhereCondition = '')
			SET @WhereCondition = @WhereCondition + ' AND';
		SET @WhereCondition = @WhereCondition + ' [ThirdPartyIdentity] LIKE ''%' + dbo.ReformatLikeStatement(@ThirdPartyIdentity) + '%''';
	END	
		
	IF NOT(@WhereCondition = '')
				SET @WhereCondition = ' WHERE '+@WhereCondition;
	
	EXEC ('SELECT [UserKey]
      ,[PersonKey]
      ,[ScopeKey]
      ,[ThirdPartyIdentity]
      ,[UserDescription]
	  ,[MonitorStatus]
      ,[UserCreatedStamp]
      ,[UserLastUpdatedStamp]
      ,[ScopeName]
      ,[ScopeCode]
      ,[ScopeType]
      ,[ScopeDescription]
  FROM [dbo].[View_DomainScopeUsers]' + @WhereCondition + ' ORDER BY [ScopeName], [ThirdPartyIdentity];');
		
END
GO

